package com.jy.dao;

import com.jy.bean.Consignee;
import com.jy.bean.User;
import com.jy.util.DBUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ConsigneeDao {
    private static final Logger log = LoggerFactory.getLogger(UserDao.class);
    static String now = new SimpleDateFormat("yyyy-MM-dd").format(new Date());

    /**
     * 查询数据库所有的商品
     * @return
     */
    public List<Consignee> consigneeList(){
        List<Consignee> Consignees = new ArrayList<>();
        String sql = "select a.consignee_id,a.consignee_name,b.name,a.consignee_price,a.consignee_amount from m_consignee as a inner join m_type as b on a.type_id = b.type_id";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Consignee consignee = new Consignee();
                consignee.setId(rs.getInt(1));
                consignee.setName(rs.getString(2));
                consignee.setType(rs.getString(3));
                consignee.setPrice(rs.getInt(4));
                consignee.setAmount(rs.getInt(5));
                Consignees.add(consignee);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return Consignees;
    }

    /**
     * 查询今日所有的订单数量
     */
    public Integer selectquantity(){
        Integer quantitys = 0;
        String sql = "SELECT COUNT(*) FROM m_order WHERE ordernumber in (SELECT ordernumber FROM m_order WHERE DATE_FORMAT(OrderSubmissionTime,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d'));\n";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                quantitys = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return quantitys;
    }

    /**
     * 查询所有的商品数量
     * @return
     */
    public Integer selectcommodity(){
        Integer commodity = 0;
        String sql = "SELECT COUNT(*) FROM m_consignee";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                commodity = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return commodity;
    }

    /**
     * 根据商品id查询数据库所有的商品
     * @return
     */
    public List<Consignee> consigneeById(String id){
        List<Consignee> Consignees = new ArrayList<>();
        String sql = "select a.consignee_id,a.consignee_name,b.name,a.consignee_price,a.consignee_amount from m_consignee as a inner join m_type as b on a.type_id = b.type_id where consignee_id=?";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                Consignee consignee = new Consignee();
                consignee.setId(rs.getInt(1));
                consignee.setName(rs.getString(2));
                consignee.setType(rs.getString(3));
                consignee.setPrice(rs.getInt(4));
                consignee.setAmount(rs.getInt(5));
                Consignees.add(consignee);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return Consignees;
    }

    /**
     * 查询数据库中的所有商品的图片
     * @return
     */
    public List<Consignee> consigneeBypicture(){
        List<Consignee> Consignees = new ArrayList<>();
        String sql = "select consignee_id,consignee_name,consignee_picture,consignee_price from m_consignee";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Consignee consignee = new Consignee();
                consignee.setId(rs.getInt(1));
                consignee.setName(rs.getString(2));
                consignee.setPicture(rs.getString(3));
                consignee.setPrice(rs.getInt(4));
                Consignees.add(consignee);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return Consignees;
    }

    /**
     * 根据用户搜索手机名称来查询数据库中的所有商品的图片、商品名称、商品价格
     * @return
     */
    public List<Consignee> consigneeByName(String name){
        List<Consignee> Consignees = new ArrayList<>();
        String sql = "select consignee_name,consignee_picture,consignee_price from m_consignee where consignee_name like '%"+name+"%'";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Consignee consignee = new Consignee();
                consignee.setName(rs.getString(1));
                consignee.setPicture(rs.getString(2));
                consignee.setPrice(rs.getInt(3));
                Consignees.add(consignee);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return Consignees;
    }

    /**
     * 根据商品id来查询数据库中的所有商品的图片、商品名称、商品价格
     * @return
     */
    public List<Consignee> selectConsigneeById(String id){
        List<Consignee> Consignees = new ArrayList<>();
        String sql = "select consignee_name,consignee_picture,consignee_price from m_consignee where consignee_id=?";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                Consignee consignee = new Consignee();
                consignee.setName(rs.getString(1));
                consignee.setPicture(rs.getString(2));
                consignee.setPrice(rs.getInt(3));
                Consignees.add(consignee);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return Consignees;
    }

    /**
     * 面对对象的方法把sql语句全部集合起来
     * @param params
     * @return
     */
    public List<Consignee> selectByParams(Consignee params){
        List<Consignee> consignees = new ArrayList<>();
        String sql = createSql(params);
        log.debug("sql:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Consignee consignee = new Consignee();
                consignee.setId(rs.getInt(1));
                consignee.setName(rs.getString(2));
                consignee.setType(rs.getString(3));
                consignee.setPrice(rs.getInt(4));
                consignees.add(consignee);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return consignees;
    }

    private static String createSql(Consignee consignee) {
        String sql = "SELECT a.consignee_id,a.consignee_name,b.name,a.consignee_price FROM m_consignee a INNER JOIN m_type b ON a.type_id=b.type_id where 1=1";
        if (consignee.getId() != null){
            sql += (" and a.consignee_id like '%"+ consignee.getId() +"%'");
        }
        if (consignee.getName() != null){
            sql += (" and a.consignee_name like '%"+ consignee.getName() +"%'");
        }
        if (consignee.getType() != null){
            sql += (" and b.name like '%" + consignee.getType() +"%'");
        }
        if (consignee.getPrice() != null){
            sql += (" and a.consignee_price like '%" + consignee.getPrice() +"%'");
        }
        log.debug("sql" + sql);
        return sql;
    }

    /**
     * 查询m_type所有的商品类型
     * @return
     */
    public List<String> consigneetype(){
        List<String> strings = new ArrayList<>();
        String sql = "SELECT name from m_type GROUP BY name";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                strings.add(rs.getString(1));
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return strings;
    }

    /**
     * 查询当前添加的商品数据库是否存在
     * @param id
     * @return
     */
    public Boolean selelctconsigneeByid(String id,String name){
        Boolean favg = false;
        String sql = "SELECT * from m_consignee WHERE consignee_id=? or consignee_name=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,id);
            ps.setString(2,name);
            rs = ps.executeQuery();
            while (rs.next()){
                favg = true;
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 获取当前数据库最大的id
     * @return
     */
    public static Integer selelctBymaxid(){
        Integer id = 0;
        String sql = "SELECT MAX(consignee_id) from m_consignee";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                id = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return id;
    }

    /**
     * 根据当前商品名称查询当前商品的id
     * @return
     */
    public Integer selelctconsigneeBymaxid(String name){
        Integer id = 0;
        String sql = "SELECT consignee_id from m_consignee where consignee_name=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while (rs.next()){
                id = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return id;
    }

    /**
     * 添加一个商品
     * @param consignee
     * @return
     */
    public boolean insertConsignee(Consignee consignee){
        boolean favg = false;
        String sql = "insert INTO m_consignee (consignee_id,consignee_name,type_id,consignee_price) VALUES (?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,selelctBymaxid()+1);
            ps.setString(2,consignee.getName());
            ps.setInt(3,consigneeBytype_name(consignee.getType()));
            ps.setInt(4,consignee.getPrice());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally{
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 查询当前商品的类型的在类型表的id
     * @return
     */
    public static Integer consigneeBytype_name(String name){
        Integer type = 0;
        String sql = "SELECT type_id FROM m_type WHERE name = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();
            while (rs.next()){
                type = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return type;
    }

    /**
     * 给添加商品的信息在添加商品的图片
     * @param name
     * @param img
     * @return
     */
    public Boolean updateconsigneeImg(String name,String img){
        boolean favg = false;
        String sql = "UPDATE m_consignee SET consignee_picture=? WHERE consignee_name=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,img);
            ps.setString(2,name);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 根据商品名称来修改商品的
     * 商品类型
     * 商品单价
     * 商品图片
     * @param consignee
     * @return
     */
    public Boolean updateMerchandise(Consignee consignee){
        boolean favg = false;
        String sql=Updatesql(consignee);
        log.debug("sql:" +sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    public static String Updatesql(Consignee consignee){
        //UPDATE m_consignee SET type_id=?,consignee_price=?,consignee_picture=? WHERE consignee_name=?
        String sql = "UPDATE m_consignee SET type_id=";
        if (consignee.getType() != null){
            sql += ""+consigneeBytype_name(consignee.getType());
        }
        if (consignee.getPrice() != null){
            sql += ",consignee_price="+consignee.getPrice();
        }
        if (consignee.getPicture() != null){
            sql += ",consignee_picture="+consignee.getPicture();
        }
        sql += " where consignee_name='"+consignee.getName()+"';";
        return sql;
    }

    /**
     * 根据商品名称来删除商品
     * @param name
     * @return
     */
    public boolean Delete_goods(String name){
        boolean favg = false;
        String sql = "Delete FROM m_consignee WHERE consignee_name=?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }

        return favg;
    }

    public static void main(String[] args) {
//        List<Consignee> consignees = selectByParams(new Consignee(null,null,null,null,null));
//        log.debug(String.valueOf(consignees));
//        List<String> strings = consigneetype();
//        log.debug(String.valueOf(strings));
//        log.debug(String.valueOf(insertConsignee(new Consignee(null,"的娃娃","手机",59999,null,null))));
//        log.debug(Updatesql(new Consignee(null,"的娃娃","手机",59999,null,null)));
    }
}
